package com.junxonline.plat.service.database.impl;

import com.alibaba.druid.pool.DruidDataSource;
import com.junxonline.common.dao.pojo.JsTree;
import com.junxonline.common.dao.pojo.JsTreeState;
import com.junxonline.common.exception.CommonBizException;
import com.junxonline.common.util.CommonUtils;
import com.junxonline.common.util.HashKeyUtils;
import com.junxonline.common.util.ShortUUIDUtils;
import com.junxonline.plat.dao.model.*;
import com.junxonline.plat.dao.repository.*;
import com.junxonline.plat.dao.vo.*;
import com.junxonline.plat.service.base.impl.BaseServiceImpl;
import com.junxonline.plat.service.database.DatabaseService;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.xml.crypto.Data;
import java.io.*;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
@Transactional
public class DatabaseServiceImpl extends BaseServiceImpl implements DatabaseService {

    private Logger logger = LogManager.getLogger();

    @Value("${wms.mysql.path}")
    private String mysqlPath;

    @Value("${wms.database.save.path}")
    private String savePath;

    @Value("${spring.datasource.host}")
    private String host;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Autowired
    private DatabaseRepository databaseRepository;

    @Autowired
    private DatabaseTableRepository tableRepository;

    @Autowired
    private UserRepository userRepository;

    @Resource
    private DruidDataSource druidDataSource;

    /**
     * 获取数据库备份树数据
     *
     * @return
     */
    @Override
    public List<JsTree> getDatabaseTree() {

        List<Database> databases = databaseRepository.findAll(new Sort(Sort.Direction.ASC, "databaseId"));
        List<String> dateGroup = new ArrayList<>();
        List<JsTree> jsTrees = new ArrayList<>();

        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");

        for (Database database : databases) {
            dateGroup.add(simpleDateFormat.format(database.getDatabaseDate()));
        }

        dateGroup = CommonUtils.removeSameItem(dateGroup);

        for (String date : dateGroup) {

            // 构建顶级数据库备份文件
            JsTree parentJsTree = new JsTree();
            parentJsTree.setId(date.replace("-", ""));
            parentJsTree.setText(date);
            parentJsTree.setParent("#");
            parentJsTree.setState(new JsTreeState(true, false, false));
            parentJsTree.setIcon("fa fa-calendar icon-state-default");
            jsTrees.add(parentJsTree);

        }

        for (Database database : databases) {

            // 构建顶级数据库备份文件
            JsTree databaseJsTree = new JsTree();
            databaseJsTree.setId(database.getDatabaseId().toString().replace("-", ""));
            databaseJsTree.setText(database.getDatabaseName());
            databaseJsTree.setParent(simpleDateFormat.format(database.getDatabaseDate()).replace("-", ""));
            databaseJsTree.setState(new JsTreeState(false, false, false));

            if (database.getDatabaseType() == 0) {
                databaseJsTree.setIcon("fa fa-cloud-upload icon-state-info");
            }
            else {
                databaseJsTree.setIcon("fa fa-cloud-download icon-state-success");
            }

            jsTrees.add(databaseJsTree);

            List<Map<String, Object>> tables = databaseRepository.findTablesByDatabaseId(database.getDatabaseId());

            for (Map<String, Object> map : tables) {
                BigInteger tableId = (BigInteger) map.get("table_id");
                String tableName = (String) map.get("table_name");

                // 构建顶级数据库备份文件
                JsTree tableJsTree = new JsTree();
                tableJsTree.setId(tableId.toString());
                tableJsTree.setText(tableName);
                tableJsTree.setParent(database.getDatabaseId().toString());
                tableJsTree.setIcon("fa fa-table icon-state-defalut");
                tableJsTree.setState(null);
                jsTrees.add(tableJsTree);
            }

        }

        return jsTrees;
    }

    /**
     * 获取当前系统数据库树数据
     *
     * @return
     */
    @Override
    public List<JsTree> getSystemDatabaseTree() {

        List<JsTree> jsTrees = new ArrayList<>();

        String databaseName = "";
        List<String> tables = new ArrayList<>();

        // 获取当前数据库库表信息
        Connection connection = null;
        try {
            connection = druidDataSource.getConnection();
            DatabaseMetaData dbMetaData = connection.getMetaData();
            ResultSet rs = dbMetaData.getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {
                databaseName = rs.getString("TABLE_CAT");
                String tableName = rs.getString("TABLE_NAME");
                if (tableName.equals("sys_database") || tableName.equals("sys_database_table")) {
                    continue;
                }
                tables.add(rs.getString("TABLE_NAME"));
            }
        } catch (Exception e) {
            throw CommonBizException.SYSTEM_ERROR;
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                    connection = null;
                }
            } catch (Exception e) {
                throw CommonBizException.SYSTEM_ERROR;
            }
        }

        // 构建顶级数据库
        JsTree parentJsTree = new JsTree();
        parentJsTree.setId("0");
        parentJsTree.setText(databaseName);
        parentJsTree.setParent("#");
        parentJsTree.setState(new JsTreeState(true, false, false));
        parentJsTree.setIcon("fa fa-database icon-state-default");
        jsTrees.add(parentJsTree);

        // 构建当前系统数据表树
        for (int i = 1; i < tables.size(); i++) {
            JsTree jsTree = new JsTree();
            jsTree.setId(String.valueOf(i));
            jsTree.setText(tables.get(i));
            jsTree.setParent("0");
            jsTree.setState(new JsTreeState(true, false, false));
            jsTree.setIcon("fa fa-table icon-state-defalut");
            jsTrees.add(jsTree);
        }

        return jsTrees;

    }

    /**
     * 筛选查询数据库备份数据
     *
     * @param pageVO
     * @param vo
     * @return
     */
    @Override
    public PageVO queryDatabase(PageVO pageVO, QueryDatabaseVO vo) {
        PageRequest pageRequest = PageRequest.of(pageVO.getPage() - 1, pageVO.getLimit(), new Sort(Sort.Direction.ASC, "databaseId"));

        Page<Database> page = databaseRepository.findAll(new Specification<Database>() {
            @Override
            public Predicate toPredicate(Root<Database> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {

                List<Predicate> params = new ArrayList<>();
                String databaseName = vo.getDatabaseName();
                Integer databaseType = vo.getDatabaseType();
                String databaseDate = vo.getDatabaseDate();

                if (CommonUtils.isNotEmpty(databaseName)) {
                    params.add(criteriaBuilder.like(root.get("databaseName"), "%" + databaseName + "%"));
                }

                if (CommonUtils.isNotEmpty(databaseType)) {
                    params.add(criteriaBuilder.equal(root.get("databaseType"), databaseType));
                }

                if (CommonUtils.isNotEmpty(databaseDate)) {
                    params.add(criteriaBuilder.like(root.get("databaseDate").as(String.class), "%" + databaseDate + "%"));
                }

                return criteriaBuilder.and(params.toArray(new Predicate[params.size()]));
            }
        }, pageRequest);

        List<Database> databases = page.getContent();
        List<DatabaseVO> databaseVOs = new ArrayList<>();

        for (Database database : databases) {

            // 替换菜单类型
            switch (database.getDatabaseType()) {
                case 0:
                    database.setDatabaseTypeString("备份");
                    break;
                case 1:
                    database.setDatabaseTypeString("还原");
                    break;
                default:
                    break;
            }

            DatabaseVO databaseVO = new DatabaseVO();
            databaseVO.setDatabaseId(database.getDatabaseId());
            databaseVO.setDatabaseName(database.getDatabaseName());
            databaseVO.setDatabaseType(database.getDatabaseType());
            databaseVO.setDatabaseTypeString(database.getDatabaseTypeString());
            databaseVO.setDatabaseHash(database.getDatabaseHash());
            databaseVO.setDatabaseActionTime(database.getDatabaseActionTime());
            databaseVO.setDatabaseDate(database.getDatabaseDate());

            User user = userRepository.findByUserId(database.getCreateUserId());
            if (CommonUtils.isNotEmpty(user)) {
                databaseVO.setCreateUserId(user.getUserId());
                databaseVO.setCreateUsername(user.getUsername());
            }

            databaseVOs.add(databaseVO);

        }

        pageVO.setTotal(page.getTotalElements());
        pageVO.setData(databaseVOs);

        return pageVO;
    }

    /**
     * 备份数据库
     *
     * @param databaseIds
     * @param userId
     * @return
     */
    @Override
    public boolean databaseBackup(Long[] databaseIds, Long userId) {

        List<String> tables = new ArrayList<>();
        String databaseName = "";
        String fileName = "";

        fileName = ShortUUIDUtils.getShortUUID();

        // 获取当前数据库库表信息
        Connection connection = null;
        try {
            connection = druidDataSource.getConnection();
            DatabaseMetaData dbMetaData = connection.getMetaData();
            ResultSet rs = dbMetaData.getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {
                databaseName = rs.getString("TABLE_CAT");
                String tableName = rs.getString("TABLE_NAME");
                if (tableName.equals("sys_database") || tableName.equals("sys_database_table")) {
                    continue;
                }
                tables.add(rs.getString("TABLE_NAME"));
            }
        } catch (Exception e) {
            throw CommonBizException.SYSTEM_ERROR;
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                    connection = null;
                }
            } catch (Exception e) {
                throw CommonBizException.SYSTEM_ERROR;
            }
        }

        Date currentTime = new Date();

        // 将记录写入数据库
        Database database = new Database();
        database.setDatabaseType(0);
        String hashKey = HashKeyUtils.hashKeyForDisk(fileName);
        database.setDatabaseHash(hashKey);
        database.setDatabaseName(fileName);
        database.setDatabaseActionTime(currentTime);
        database.setDatabaseDate(currentTime);
        database.setCreateUserId(userId);
        databaseRepository.save(database);

        for (int i = 0; i < databaseIds.length - 1; i++) {
            DatabaseTable table = new DatabaseTable();
            table.setTableName(tables.get(databaseIds[i].intValue()));
            table.setDatabase(database);
            tableRepository.save(table);
        }

        PrintWriter printWriter = null;
        BufferedReader bufferedReader = null;
        OutputStreamWriter outputStreamWriter = null;
        FileOutputStream fileOutputStream = null;

        try {

            File file = new File(savePath);
            if (!file.exists()) {
                file.mkdirs();
            }

            fileOutputStream = new FileOutputStream(savePath + fileName + ".sql");
            outputStreamWriter = new OutputStreamWriter(fileOutputStream, "utf-8");
            printWriter = new PrintWriter(outputStreamWriter);

            StringBuilder command = new StringBuilder();
//            command.append(mysqlPath);
            command.append("mysqldump -h");
            command.append(host);
            command.append(" -u");
            command.append(username);
            command.append(" -p");
            command.append(password);
            command.append(" --set-charset=utf-8 ");
            command.append(databaseName);

            for (int i = 0; i < databaseIds.length - 1; i++) {
                command.append(" ");
                command.append(tables.get(databaseIds[i].intValue()));
            }

            command.append(" >");
            command.append(savePath);
            command.append(fileName);
            command.append(".sql");

            logger.info("备份数据库命令：" + command.toString());

            Process process = Runtime.getRuntime().exec(command.toString());
            InputStreamReader inputStreamReader = new InputStreamReader(process.getInputStream(), "utf-8");

            bufferedReader = new BufferedReader(inputStreamReader);

            String line;
            while ((line = bufferedReader.readLine()) != null) {
                printWriter.println(line);
            }

            printWriter.flush();
            // 0 表示线程正常终止
            if (process.waitFor() == 0) {

                return true;
            }
        }
        catch (Exception e) {
            e.printStackTrace();
            throw CommonBizException.SYSTEM_ERROR;
        }
        finally {
            try {
                if (bufferedReader != null) {
                    bufferedReader.close();
                }
                if (printWriter != null) {
                    printWriter.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
                throw CommonBizException.SYSTEM_ERROR;
            }
        }
        return false;
    }


    /**
     * 还原数据库
     *
     * @param vo
     * @param userId
     * @return
     */
    @Override
    public boolean databaseRestore(DatabaseVO vo, Long userId) {

        Database tempDatabase = databaseRepository.findByDatabaseId(vo.getDatabaseId());

        if (CommonUtils.isEmpty(tempDatabase)) {
            throw CommonBizException.SYSTEM_ERROR;
        }

        String databaseName = "";

        // 获取当前数据库库表信息
        Connection connection = null;
        try {
            connection = druidDataSource.getConnection();
            DatabaseMetaData dbMetaData = connection.getMetaData();
            ResultSet rs = dbMetaData.getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {
                databaseName = rs.getString("TABLE_CAT");
            }
        } catch (Exception e) {
            throw CommonBizException.SYSTEM_ERROR;
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                    connection = null;
                }
            } catch (Exception e) {
                throw CommonBizException.SYSTEM_ERROR;
            }
        }

        List<Map<String, Object>> tables = databaseRepository.findTablesByDatabaseId(tempDatabase.getDatabaseId());

        Date currentTime = new Date();

        // 将记录写入数据库
        Database database = new Database();
        database.setDatabaseType(1);

        database.setDatabaseName(tempDatabase.getDatabaseName());
        database.setDatabaseActionTime(currentTime);
        database.setDatabaseDate(currentTime);
        database.setCreateUserId(userId);
        databaseRepository.save(database);

        for (Map<String, Object> map : tables) {
            String name = (String) map.get("table_name");
            DatabaseTable table = new DatabaseTable();
            table.setTableName(name);
            table.setDatabase(database);
            tableRepository.save(table);
        }

        try {

            File file = new File(savePath);
            if (!file.exists()) {
                file.mkdirs();
            }

            StringBuilder command = new StringBuilder();
//            command.append(mysqlPath);
            command.append("mysql -h");
            command.append(host);
            command.append(" -u");
            command.append(username);
            command.append(" -p");
            command.append(password);
            command.append(" ");
            command.append(databaseName);

            String[] cmd = {command.toString() + " <" + savePath + tempDatabase.getDatabaseName() + ".sql"};

            logger.info("还原数据库命令：" + cmd[0] + cmd[1] + cmd[2]);

            Process process = Runtime.getRuntime().exec(cmd);

            // 0 表示线程正常终止
            if (process.waitFor() == 0) {

                return true;
            }
        }
        catch (Exception e) {
            e.printStackTrace();
            throw CommonBizException.SYSTEM_ERROR;
        }
        return false;
    }

    /**
     * 还原数据库
     *
     * @return
     */
    @Override
    public boolean databaseRestore() {

        String databaseName = "";

        // 获取当前数据库库表信息
        Connection connection = null;
        try {
            connection = druidDataSource.getConnection();
            DatabaseMetaData dbMetaData = connection.getMetaData();
            ResultSet rs = dbMetaData.getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {
                databaseName = rs.getString("TABLE_CAT");
            }
        } catch (Exception e) {
            throw CommonBizException.SYSTEM_ERROR;
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                    connection = null;
                }
            } catch (Exception e) {
                throw CommonBizException.SYSTEM_ERROR;
            }
        }

        try {

            File file = new File(savePath);
            if (!file.exists()) {
                file.mkdirs();
            }

            StringBuilder command = new StringBuilder();
//            command.append(mysqlPath);
            command.append("mysql -h");
            command.append(host);
            command.append(" -u");
            command.append(username);
            command.append(" -p");
            command.append(password);
            command.append(" ");
            command.append(databaseName);

            String[] cmd = {command.toString() + " <" + savePath + databaseName + ".sql"};

            logger.info("还原数据库命令：" + cmd[0] + cmd[1] + cmd[2]);

            Process process = Runtime.getRuntime().exec(cmd);

            // 0 表示线程正常终止
            if (process.waitFor() == 0) {

                return true;
            }
        }
        catch (Exception e) {
            e.printStackTrace();
            throw CommonBizException.SYSTEM_ERROR;
        }
        return false;
    }

}
